package com.cloudbroker.openapi.manage.util;

import com.cloudbroker.bcs.common.util.ReflectionUtil;
import com.cloudbroker.bcs.common.util.StringUtil;
import com.cloudbroker.bcs.common.util.ValueUtil;
import com.cloudbroker.bcs.platform.ltb.bizapi.service.leader.back.InviteLogBackVo;
import com.cloudbroker.bcs.platform.ltb.bizapi.service.push.vo.BatchPushForm;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.*;
import jxl.write.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel导入导出工具类
 */

public class ExcelUtil {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    
    /**
     * 解析excel文件
     * @param multipartFile
     * @return
     */
    public static List<InviteLogBackVo> parseFile(MultipartFile multipartFile) {
        List<InviteLogBackVo> list = new ArrayList<>();
        org.apache.poi.ss.usermodel.Workbook wb = null;
        try {
            InputStream fis = multipartFile.getInputStream();
            if (multipartFile.getOriginalFilename().endsWith("xls")) {
                wb = new HSSFWorkbook(fis);
            } else if (multipartFile.getOriginalFilename().endsWith("xlsx")) {
                wb = new XSSFWorkbook(fis);
            }
            
            // 得到总行数
            Sheet sheet = wb.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                String contents = getCellValue(row.getCell(1));
                if (!StringUtil.isBlank(contents) && contents.matches("^(1[34578]\\d{9})?$")) {
                    InviteLogBackVo form = new InviteLogBackVo();
//                    InviteLog.setMobile(contents);
                    list.add(form);
                }
            }
        } catch (IOException e) {
            logger.error("excel文件解析错误:" + e.getMessage());
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }


    /**
     * 解析批量推送excel文件
     * @param multipartFile
     * @return
     * */
    public static List<BatchPushForm> parseBatchPushFile(MultipartFile multipartFile) {
        List<BatchPushForm> list = new ArrayList<>();
        org.apache.poi.ss.usermodel.Workbook wb = null;
        try {
            InputStream fis = multipartFile.getInputStream();
            if (multipartFile.getOriginalFilename().endsWith("xls")) {
                wb = new HSSFWorkbook(fis);
            }
            else if (multipartFile.getOriginalFilename().endsWith("xlsx")) {
                wb = new XSSFWorkbook(fis);
            }
            // 得到总行数
            Sheet sheet = wb.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                //解析格式 第一列表示用户昵称 第二列表示用户手机号
                BatchPushForm batchPushForm = new BatchPushForm();
                boolean add_flag = false;
                row = sheet.getRow(i);
                String alias_name = getCellValue(row.getCell(0));
                String mobile = getCellValue(row.getCell(1));
                if (!StringUtil.isBlank(alias_name) && !StringUtil.isBlank(mobile) && mobile.matches("^(1[34578]\\d{9})?$")) {
                    batchPushForm.setAlias_name(alias_name);
                    batchPushForm.setMobile(mobile);
                    add_flag = true;
                }
                if (add_flag) {
                    list.add(batchPushForm);
                }
            }
        } catch (IOException e) {
            logger.error("excel文件解析错误:" + e.getMessage());
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }


    /**
     * 导出excel批量推送模版
     * */
    public static void outputBatchPushFile(HttpServletResponse res) {
        OutputStream os = null;
        try {
            os = res.getOutputStream(); // 取得输出流
            res.reset(); // 清空输出流
            res.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("批量推送模版", "UTF8")
                    + ".xls"); // 设定输出文件头
            res.setContentType("application/msexcel"); // 定义输出类型
        } catch (IOException ex) {
            logger.error("流操作错误:" + ex.getMessage());
        }

        WritableWorkbook workbook = null;
        try {
            //创建新的Excel
            workbook = Workbook.createWorkbook(os);
            // 在Excel工作簿中建一工作表，其名为:第一页
            jxl.write.WritableSheet wsheet = workbook.createSheet("批量推送", 0); // sheet();
            // 基本的就是创建一个 工作簿Excel.---> 然后就是这个Excel的页.--->最后写入数据.
            // 设置每一列的宽度.第一个参数是列,第二个是多宽按厘米记,
            wsheet.setColumnView(0, 30);// 第1列
            wsheet.setColumnView(1, 30);// 第2列
            WritableFont font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat format = new WritableCellFormat(font);
            format.setAlignment(Alignment.CENTRE);
            Label wlabel0 = new Label(0, 0, "昵称", format);
            Label wlabel1 = new Label(1, 0, "手机号", format);
            wsheet.addCell(wlabel0);
            wsheet.addCell(wlabel1);
            workbook.write(); // 写入文件
        } catch (WriteException ex1) {
            logger.error("WriteException:" + ex1.getMessage());
        } catch (IOException ex2) {
            logger.error("IOException:" + ex2.getMessage());
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }




    
    private static String getCellValue(Cell cell) {
        String ret;
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                ret = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                ret = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                org.apache.poi.ss.usermodel.Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                ret = getCellValue(evaluator.evaluateInCell(cell));
                break;
            case Cell.CELL_TYPE_NUMERIC:
                ret = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = "";
        }
        if (ret == null) {
            return "";
        }
        return ret.trim();
    }
    
    public static void outputFile(HttpServletResponse res, List<InviteLogBackVo> mobileList) {
        OutputStream os = null;
        try {
            os = res.getOutputStream(); // 取得输出流
            res.reset(); // 清空输出流
            res.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("邀请列表", "UTF8")
                    + ".xls"); // 设定输出文件头
            res.setContentType("application/msexcel"); // 定义输出类型
        } catch (IOException ex) {
            logger.error("流操作错误:" + ex.getMessage());
        }
        
        WritableWorkbook workbook = null;
        try {
            // 创建新的Excel
            workbook = Workbook.createWorkbook(os);
            // 在Excel工作簿中建一工作表，其名为:第一页
            jxl.write.WritableSheet wsheet = workbook.createSheet("邀请列表", 0); // sheet();
            // 基本的就是创建一个 工作簿Excel.---> 然后就是这个Excel的页.--->最后写入数据.
            // 设置每一列的宽度.第一个参数是列,第二个是多宽按厘米记,
            wsheet.setColumnView(0, 30);// 第1列
            wsheet.setColumnView(1, 30);// 第2列
            WritableFont font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat format = new WritableCellFormat(font);
            format.setAlignment(Alignment.CENTRE);
            Label wlabel0 = new Label(0, 0, "昵称", format);
            Label wlabel1 = new Label(1, 0, "手机", format);
            Label wlabel2 = new Label(2, 0, "时间", format);
            wsheet.addCell(wlabel0);
            wsheet.addCell(wlabel1);
            wsheet.addCell(wlabel2);
            if (mobileList != null && !mobileList.isEmpty()) {
                for (int i = 1; i <= mobileList.size(); i++) {
                    InviteLogBackVo entity = mobileList.get(i - 1);
                    Label nameLable = new Label(0, i, String.valueOf(entity.getAlias_name()), format);
                    Label mobileLable = new Label(1, i, String.valueOf(entity.getMobile()), format);
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date date = new Date(entity.getCreate_time());
                    String time = simpleDateFormat.format(date);
                    Label timeLable = new Label(2, i, String.valueOf(time), format);
                    wsheet.addCell(nameLable);
                    wsheet.addCell(mobileLable);
                    wsheet.addCell(timeLable);
                }
            }
            workbook.write(); // 写入文件
        } catch (WriteException ex1) {
            logger.error("WriteException:" + ex1.getMessage());
        } catch (IOException ex2) {
            logger.error("IOException:" + ex2.getMessage());
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void exportExcel(HttpServletResponse res, String fileName, List dataList, Map<String, String> headerMap, String sheetName){
        OutputStream os = null;
        try {
            os = res.getOutputStream(); // 取得输出流
            res.reset(); // 清空输出流
            res.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF8")
                    + ".xls"); // 设定输出文件头
            res.setContentType("application/msexcel"); // 定义输出类型
        } catch (IOException ex) {
            logger.error("流操作错误:" + ex.getMessage());
        }
        exportExcel(os,dataList,headerMap,sheetName);
    }

    /**
     * @Description:(数据导出excel)
     * @author dwei 2014-7-21
     * @param
     * @return void 返回类型
     * @throws
     */
    public static void exportExcel(OutputStream os, List dataList, Map<String, String> headerMap, String sheetName) {
        try {
            WritableWorkbook wbook = Workbook.createWorkbook(os);
            jxl.write.WritableSheet wsheet = wbook.createSheet(sheetName, 0);
            wsheet.getSettings().setShowGridLines(false);
            wsheet.setColumnView(0, 2);// 设置第一列宽度

            Set<String> set = headerMap.keySet();
            String[] titles = set.toArray(new String[set.size()]);

            for (int i = 0; i < titles.length; i++) {
                wsheet.setColumnView(i + 1, 20);// 设置列宽
            }
            // 循环写入表头内容
            for (int i = 0; i < titles.length; i++) {
                String title = titles[i];
                wsheet.addCell(new Label(i + 1, 1, title, getHeaderCellStyle()));
            }
            // 写入数据
            for (int i = 0; i < dataList.size(); i++) {
                Object o = dataList.get(i);
                Map<String , Field> m = ReflectionUtil.listAllFields(o.getClass());
                for (int j = 0; j < titles.length; j++) {
                    String value = ValueUtil.getString(ReflectionUtil.getValueRough(o , m.get(titles[j])));
                    wsheet.addCell(new Label(j + 1, i + 2, value, getBodyCellStyle()));
                }
            }
            wbook.write();
            wbook.close();
            os.flush();
            os.close();
            os = null;

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static WritableCellFormat getHeaderCellStyle() {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
        WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);
        try {
            // 添加字体设置
            headerFormat.setFont(font);
            // 设置单元格背景色：表头为 灰色
            headerFormat.setBackground(Colour.GRAY_25);
            // 设置表头表格边框样式
            // 整个表格线为粗线、黑色
            headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            // 表头内容水平居中显示
            headerFormat.setAlignment(Alignment.CENTRE);
        } catch (WriteException e) {
            System.out.println("表头单元格样式设置失败！");
        }
        return headerFormat;
    }

    public static WritableCellFormat getBodyCellStyle() {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
        WritableCellFormat bodyFormat = new WritableCellFormat(font);
        try {
            // 设置单元格背景色：表体为白色
            bodyFormat.setBackground(Colour.WHITE);
            // 设置表头表格边框样式
            // 整个表格线为细线、黑色
            bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        } catch (WriteException e) {
            System.out.println("表体单元格样式设置失败！");
        }
        return bodyFormat;
    }

}